# coding: UTF-8
import os
import datetime
import cx_Oracle
import zipfile
import ftplib
import uuid
import json

os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'

days = [17]
ids = ['01']
profiles = ['cea-global-prd']

def getFTPFile(id,profile,date):
    # path = '/home/AA/backups/{}'.format(date)
    path = '/Users/songxiangyang/Documents/works/work/testdata/easternlines/{}'.format(date)
    isExists = os.path.exists(path)
    if not isExists:
        os.makedirs(path)
        print(path + ' 创建成功')

    f = ftplib.FTP('ftp4.omniture.com')
    f.login('cea_df', '5nq4otfs')
    file_remote = '/ceair/{}-{}_{}.tsv.zip'.format(id,profile,date)
    file_local = '/Users/songxiangyang/Documents/works/work/testdata/easternlines/{}/{}-{}_{}.tsv.zip'.format(date,id,profile,date)
    bufsize = 1024
    fp = open(file_local, 'wb')
    try:
        f.retrbinary('RETR %s' % file_remote, fp.write, bufsize)
        return True
    except ftplib.error_perm:
        os.unlink(file_local)
        print('file error:',file_remote)
        return False
    finally:
        fp.close()


def getLastDay(day=1):
    dateFormat = "%Y-%m-%d"
    h = datetime.datetime.now() - datetime.timedelta(day)
    lastDay = h.strftime(dateFormat)
    return lastDay


def getZipData(id, profile,date):
    file_local_zip = '/Users/songxiangyang/Documents/works/work/testdata/easternlines/{}/{}-{}_{}.tsv.zip'.format(date,id, profile, date)
    zip_file_data = '{}-{}_{}.tsv'.format(id, profile, date)
    # zip_file_data = '01-cea-global-prd_2018-09-23.tsv'
    data_zip_file = zipfile.ZipFile(file_local_zip)
    count = 1
    row_data = []
    with data_zip_file.open(zip_file_data) as rows:
        for row in rows:
            column_data = (date,)
            columns = []
            try:
                columns = str(row).split('\t')
            except Exception as err:
                print(err)
                continue

            # if(len(columns)!=1005):
            #     continue
            # for num in range(1, 37):
            #     del columns[747]

            for column in columns:
                column_data = column_data + (str(column.strip()),)
            print (column_data)
            row_data.append(column_data)
            # print(row_data)
            # if count == 1000:
            #     insertData(profile,row_data)
            #     row_data = []
            #     count = 0
            # else:
            #     count = count + 1
        # insertData(profile, row_data)
        print(row_data)

def deleteData(profile,date):
    conn = cx_Oracle.connect('AA', 'AAaa_2018', '172.28.96.201:1531/LOGDB')
    cur = conn.cursor()
    try:
        delete_str = "delete from {} where create_date = TO_DATE('{}','yyyy-MM-dd') ".format(profile.replace('-','_'),date)
        cur.execute(delete_str)
        conn.commit()
        print('delete data is ok')
    except Exception as err:
        print(err)
    finally:
        cur.close()
        conn.close()

def insertData(profile,data):
    conn = cx_Oracle.connect('AA', 'AAaa_2018', '172.28.96.201:1531/LOGDB')
    cur = conn.cursor()
    try:
        cur.executemany(
            "insert into {}(create_date,accept_language ,aemassetid ,aemassetsource ,aemclickedassetid ,browser ,browser_height ,browser_width ,c_color ,campaign ,carrier ,channel ,click_action ,click_action_type ,click_context ,click_context_type ,click_sourceid ,click_tag ,code_ver ,color ,connection_type ,cookies ,country ,ct_connect_type ,curr_factor ,curr_rate ,currency ,cust_hit_time_gmt ,cust_visid ,daily_visitor ,date_time ,domain ,duplicate_events ,duplicate_purchase ,duplicated_from ,ef_id ,evar1 ,evar2 ,evar3 ,evar4 ,evar5 ,evar6 ,evar7 ,evar8 ,evar9 ,evar10 ,evar11 ,evar12 ,evar13 ,evar14 ,evar15 ,evar16 ,evar17 ,evar18 ,evar19 ,evar20 ,evar21 ,evar22 ,evar23 ,evar24 ,evar25 ,evar26 ,evar27 ,evar28 ,evar29 ,evar30 ,evar31 ,evar32 ,evar33 ,evar34 ,evar35 ,evar36 ,evar37 ,evar38 ,evar39 ,evar40 ,evar41 ,evar42 ,evar43 ,evar44 ,evar45 ,evar46 ,evar47 ,evar48 ,evar49 ,evar50 ,evar51 ,evar52 ,evar53 ,evar54 ,evar55 ,evar56 ,evar57 ,evar58 ,evar59 ,evar60 ,evar61 ,evar62 ,evar63 ,evar64 ,evar65 ,evar66 ,evar67 ,evar68 ,evar69 ,evar70 ,evar71 ,evar72 ,evar73 ,evar74 ,evar75 ,evar76 ,evar77 ,evar78 ,evar79 ,evar80 ,evar81 ,evar82 ,evar83 ,evar84 ,evar85 ,evar86 ,evar87 ,evar88 ,evar89 ,evar90 ,evar91 ,evar92 ,evar93 ,evar94 ,evar95 ,evar96 ,evar97 ,evar98 ,evar99 ,evar100 ,evar101 ,evar102 ,evar103 ,evar104 ,evar105 ,evar106 ,evar107 ,evar108 ,evar109 ,evar110 ,evar111 ,evar112 ,evar113 ,evar114 ,evar115 ,evar116 ,evar117 ,evar118 ,evar119 ,evar120 ,evar121 ,evar122 ,evar123 ,evar124 ,evar125 ,evar126 ,evar127 ,evar128 ,evar129 ,evar130 ,evar131 ,evar132 ,evar133 ,evar134 ,evar135 ,evar136 ,evar137 ,evar138 ,evar139 ,evar140 ,evar141 ,evar142 ,evar143 ,evar144 ,evar145 ,evar146 ,evar147 ,evar148 ,evar149 ,evar150 ,evar151 ,evar152 ,evar153 ,evar154 ,evar155 ,evar156 ,evar157 ,evar158 ,evar159 ,evar160 ,evar161 ,evar162 ,evar163 ,evar164 ,evar165 ,evar166 ,evar167 ,evar168 ,evar169 ,evar170 ,evar171 ,evar172 ,evar173 ,evar174 ,evar175 ,evar176 ,evar177 ,evar178 ,evar179 ,evar180 ,evar181 ,evar182 ,evar183 ,evar184 ,evar185 ,evar186 ,evar187 ,evar188 ,evar189 ,evar190 ,evar191 ,evar192 ,evar193 ,evar194 ,evar195 ,evar196 ,evar197 ,evar198 ,evar199 ,evar200 ,evar201 ,evar202 ,evar203 ,evar204 ,evar205 ,evar206 ,evar207 ,evar208 ,evar209 ,evar210 ,evar211 ,evar212 ,evar213 ,evar214 ,evar215 ,evar216 ,evar217 ,evar218 ,evar219 ,evar220 ,evar221 ,evar222 ,evar223 ,evar224 ,evar225 ,evar226 ,evar227 ,evar228 ,evar229 ,evar230 ,evar231 ,evar232 ,evar233 ,evar234 ,evar235 ,evar236 ,evar237 ,evar238 ,evar239 ,evar240 ,evar241 ,evar242 ,evar243 ,evar244 ,evar245 ,evar246 ,evar247 ,evar248 ,evar249 ,evar250 ,event_list ,exclude_hit ,first_hit_page_url ,first_hit_pagename ,first_hit_ref_domain ,first_hit_ref_type ,first_hit_referrer ,first_hit_time_gmt ,geo_city ,geo_country ,geo_dma ,geo_region ,geo_zip ,hier1 ,hier2 ,hier3 ,hier4 ,hier5 ,hit_source ,hit_time_gmt ,hitid_high ,hitid_low ,homepage ,hourly_visitor ,ip ,ip2 ,j_jscript ,java_enabled ,javascript ,language ,last_hit_time_gmt ,last_purchase_num ,last_purchase_time_gmt ,mc_audiences ,mcvisid ,mobile_id ,mobileaction ,mobileappid ,mobilecampaigncontent ,mobilecampaignmedium ,mobilecampaignname ,mobilecampaignsource ,mobilecampaignterm ,mobiledayofweek ,mobiledayssincefirstuse ,mobiledayssincelastuse ,mobiledevice ,mobilehourofday ,mobileinstalldate ,mobilelaunchnumber ,mobileltv ,mobilemessageid ,mobilemessageonline ,mobileosversion ,mobilepushoptin ,mobilepushpayloadid ,mobileresolution ,monthly_visitor ,mvvar1 ,mvvar2 ,mvvar3 ,namespace ,new_visit ,os ,p_plugins ,page_event ,page_event_var1 ,page_event_var2 ,page_event_var3 ,page_type ,page_url ,pagename ,paid_search ,partner_plugins ,persistent_cookie ,plugins ,pointofinterest ,pointofinterestdistance ,post_browser_height ,post_browser_width ,post_campaign ,post_channel ,post_cookies ,post_currency ,post_cust_hit_time_gmt ,post_cust_visid ,post_ef_id ,post_evar1 ,post_evar2 ,post_evar3 ,post_evar4 ,post_evar5 ,post_evar6 ,post_evar7 ,post_evar8 ,post_evar9 ,post_evar10 ,post_evar11 ,post_evar12 ,post_evar13 ,post_evar14 ,post_evar15 ,post_evar16 ,post_evar17 ,post_evar18 ,post_evar19 ,post_evar20 ,post_evar21 ,post_evar22 ,post_evar23 ,post_evar24 ,post_evar25 ,post_evar26 ,post_evar27 ,post_evar28 ,post_evar29 ,post_evar30 ,post_evar31 ,post_evar32 ,post_evar33 ,post_evar34 ,post_evar35 ,post_evar36 ,post_evar37 ,post_evar38 ,post_evar39 ,post_evar40 ,post_evar41 ,post_evar42 ,post_evar43 ,post_evar44 ,post_evar45 ,post_evar46 ,post_evar47 ,post_evar48 ,post_evar49 ,post_evar50 ,post_evar51 ,post_evar52 ,post_evar53 ,post_evar54 ,post_evar55 ,post_evar56 ,post_evar57 ,post_evar58 ,post_evar59 ,post_evar60 ,post_evar61 ,post_evar62 ,post_evar63 ,post_evar64 ,post_evar65 ,post_evar66 ,post_evar67 ,post_evar68 ,post_evar69 ,post_evar70 ,post_evar71 ,post_evar72 ,post_evar73 ,post_evar74 ,post_evar75 ,post_evar76 ,post_evar77 ,post_evar78 ,post_evar79 ,post_evar80 ,post_evar81 ,post_evar82 ,post_evar83 ,post_evar84 ,post_evar85 ,post_evar86 ,post_evar87 ,post_evar88 ,post_evar89 ,post_evar90 ,post_evar91 ,post_evar92 ,post_evar93 ,post_evar94 ,post_evar95 ,post_evar96 ,post_evar97 ,post_evar98 ,post_evar99 ,post_evar100 ,post_evar101 ,post_evar102 ,post_evar103 ,post_evar104 ,post_evar105 ,post_evar106 ,post_evar107 ,post_evar108 ,post_evar109 ,post_evar110 ,post_evar111 ,post_evar112 ,post_evar113 ,post_evar114 ,post_evar115 ,post_evar116 ,post_evar117 ,post_evar118 ,post_evar119 ,post_evar120 ,post_evar121 ,post_evar122 ,post_evar123 ,post_evar124 ,post_evar125 ,post_evar126 ,post_evar127 ,post_evar128 ,post_evar129 ,post_evar130 ,post_evar131 ,post_evar132 ,post_evar133 ,post_evar134 ,post_evar135 ,post_evar136 ,post_evar137 ,post_evar138 ,post_evar139 ,post_evar140 ,post_evar141 ,post_evar142 ,post_evar143 ,post_evar144 ,post_evar145 ,post_evar146 ,post_evar147 ,post_evar148 ,post_evar149 ,post_evar150 ,post_evar151 ,post_evar152 ,post_evar153 ,post_evar154 ,post_evar155 ,post_evar156 ,post_evar157 ,post_evar158 ,post_evar159 ,post_evar160 ,post_evar161 ,post_evar162 ,post_evar163 ,post_evar164 ,post_evar165 ,post_evar166 ,post_evar167 ,post_evar168 ,post_evar169 ,post_evar170 ,post_evar171 ,post_evar172 ,post_evar173 ,post_evar174 ,post_evar175 ,post_evar176 ,post_evar177 ,post_evar178 ,post_evar179 ,post_evar180 ,post_evar181 ,post_evar182 ,post_evar183 ,post_evar184 ,post_evar185 ,post_evar186 ,post_evar187 ,post_evar188 ,post_evar189 ,post_evar190 ,post_evar191 ,post_evar192 ,post_evar193 ,post_evar194 ,post_evar195 ,post_evar196 ,post_evar197 ,post_evar198 ,post_evar199 ,post_evar200 ,post_evar201 ,post_evar202 ,post_evar203 ,post_evar204 ,post_evar205 ,post_evar206 ,post_evar207 ,post_evar208 ,post_evar209 ,post_evar210 ,post_evar211 ,post_evar212 ,post_evar213 ,post_evar214 ,post_evar215 ,post_evar216 ,post_evar217 ,post_evar218 ,post_evar219 ,post_evar220 ,post_evar221 ,post_evar222 ,post_evar223 ,post_evar224 ,post_evar225 ,post_evar226 ,post_evar227 ,post_evar228 ,post_evar229 ,post_evar230 ,post_evar231 ,post_evar232 ,post_evar233 ,post_evar234 ,post_evar235 ,post_evar236 ,post_evar237 ,post_evar238 ,post_evar239 ,post_evar240 ,post_evar241 ,post_evar242 ,post_evar243 ,post_evar244 ,post_evar245 ,post_evar246 ,post_evar247 ,post_evar248 ,post_evar249 ,post_evar250 ,post_event_list ,post_hier1 ,post_hier2 ,post_hier3 ,post_hier4 ,post_hier5 ,post_java_enabled ,post_keywords ,post_mc_audiences ,post_mobileaction ,post_mobileappid ,post_mobilecampaigncontent ,post_mobilecampaignmedium ,post_mobilecampaignname ,post_mobilecampaignsource ,post_mobilecampaignterm ,post_mobiledayofweek ,post_mobiledayssincefirstuse ,post_mobiledayssincelastuse ,post_mobiledevice ,post_mobilehourofday ,post_mobileinstalldate ,post_mobilelaunchnumber ,post_mobileltv ,post_mobilemessageid ,post_mobilemessageonline ,post_mobileosversion ,post_mobilepushoptin ,post_mobilepushpayloadid ,post_mobileresolution ,post_mvvar1 ,post_mvvar2 ,post_mvvar3 ,post_page_event ,post_page_event_var1 ,post_page_event_var2 ,post_page_event_var3 ,post_page_type ,post_page_url ,post_pagename ,post_pagename_no_url ,post_partner_plugins ,post_persistent_cookie ,post_pointofinterest ,post_pointofinterestdistance ,post_product_list ,post_prop1 ,post_prop2 ,post_prop3 ,post_prop4 ,post_prop5 ,post_prop6 ,post_prop7 ,post_prop8 ,post_prop9 ,post_prop10 ,post_prop11 ,post_prop12 ,post_prop13 ,post_prop14 ,post_prop15 ,post_prop16 ,post_prop17 ,post_prop18 ,post_prop19 ,post_prop20 ,post_prop21 ,post_prop22 ,post_prop23 ,post_prop24 ,post_prop25 ,post_prop26 ,post_prop27 ,post_prop28 ,post_prop29 ,post_prop30 ,post_prop31 ,post_prop32 ,post_prop33 ,post_prop34 ,post_prop35 ,post_prop36 ,post_prop37 ,post_prop38 ,post_prop39 ,post_prop40 ,post_prop41 ,post_prop42 ,post_prop43 ,post_prop44 ,post_prop45 ,post_prop46 ,post_prop47 ,post_prop48 ,post_prop49 ,post_prop50 ,post_prop51 ,post_prop52 ,post_prop53 ,post_prop54 ,post_prop55 ,post_prop56 ,post_prop57 ,post_prop58 ,post_prop59 ,post_prop60 ,post_prop61 ,post_prop62 ,post_prop63 ,post_prop64 ,post_prop65 ,post_prop66 ,post_prop67 ,post_prop68 ,post_prop69 ,post_prop70 ,post_prop71 ,post_prop72 ,post_prop73 ,post_prop74 ,post_prop75 ,post_purchaseid ,post_referrer ,post_s_kwcid ,post_search_engine ,post_state ,post_survey ,post_t_time_info ,post_tnt ,post_tnt_action ,post_transactionid ,post_video ,post_videoad ,post_videoadinpod ,post_videoadlength ,post_videoadname ,post_videoadplayername ,post_videoadpod ,post_videochannel ,post_videochapter ,post_videocontenttype ,post_videolength ,post_videoname ,post_videopath ,post_videoplayername ,post_videoqoebitrateaverageeva ,post_videoqoebitratechangecoun ,post_videoqoebuffercountevar ,post_videoqoebuffertimeevar ,post_videoqoedroppedframecount ,post_videoqoeerrorcountevar ,post_videoqoetimetostartevar ,post_videosegment ,post_visid_high ,post_visid_low ,post_visid_type ,post_zip ,prev_page ,product_list ,product_merchandising ,prop1 ,prop2 ,prop3 ,prop4 ,prop5 ,prop6 ,prop7 ,prop8 ,prop9 ,prop10 ,prop11 ,prop12 ,prop13 ,prop14 ,prop15 ,prop16 ,prop17 ,prop18 ,prop19 ,prop20 ,prop21 ,prop22 ,prop23 ,prop24 ,prop25 ,prop26 ,prop27 ,prop28 ,prop29 ,prop30 ,prop31 ,prop32 ,prop33 ,prop34 ,prop35 ,prop36 ,prop37 ,prop38 ,prop39 ,prop40 ,prop41 ,prop42 ,prop43 ,prop44 ,prop45 ,prop46 ,prop47 ,prop48 ,prop49 ,prop50 ,prop51 ,prop52 ,prop53 ,prop54 ,prop55 ,prop56 ,prop57 ,prop58 ,prop59 ,prop60 ,prop61 ,prop62 ,prop63 ,prop64 ,prop65 ,prop66 ,prop67 ,prop68 ,prop69 ,prop70 ,prop71 ,prop72 ,prop73 ,prop74 ,prop75 ,purchaseid ,quarterly_visitor ,ref_domain ,ref_type ,referrer ,resolution ,s_kwcid ,s_resolution ,sampled_hit ,search_engine ,search_page_num ,secondary_hit ,service ,socialaccountandappids ,socialassettrackingcode ,socialauthor ,socialaveragesentiment ,socialaveragesentiment_depreca ,socialcontentprovider ,socialfbstories ,socialfbstorytellers ,socialinteractioncount ,socialinteractiontype ,sociallanguage ,sociallatlong ,sociallikeadds ,sociallink ,sociallink_deprecated ,socialmentions ,socialowneddefinitioninsightty ,socialowneddefinitioninsightva ,socialowneddefinitionmetric ,socialowneddefinitionpropertyv ,socialownedpostids ,socialownedpropertyid ,socialownedpropertyname ,socialownedpropertypropertyvsa ,socialpageviews ,socialpostviews ,socialproperty ,socialproperty_deprecated ,socialpubcomments ,socialpubposts ,socialpubrecommends ,socialpubsubscribers ,socialterm ,socialtermslist ,socialtermslist_deprecated ,socialtotalsentiment ,sourceid ,state ,stats_server ,t_time_info ,tnt ,tnt_action ,tnt_post_vista ,transactionid ,truncated_hit ,ua_color ,ua_os ,ua_pixels ,user_agent ,user_hash ,user_server ,userid ,username ,va_closer_detail ,va_closer_id ,va_finder_detail ,va_finder_id ,va_instance_event ,va_new_engagement ,video ,videoad ,videoadinpod ,videoadlength ,videoadname ,videoadplayername ,videoadpod ,videochannel ,videochapter ,videocontenttype ,videolength ,videoname ,videopath ,videoplayername ,videoqoebitrateaverageevar ,videoqoebitratechangecountevar ,videoqoebuffercountevar ,videoqoebuffertimeevar ,videoqoedroppedframecountevar ,videoqoeerrorcountevar ,videoqoetimetostartevar ,videosegment ,visid_high ,visid_low ,visid_new ,visid_timestamp ,visid_type ,visit_keywords ,visit_num ,visit_page_num ,visit_ref_domain ,visit_ref_type ,visit_referrer ,visit_search_engine ,visit_start_page_url ,visit_start_pagename ,visit_start_time_gmt ,weekly_visitor ,yearly_visitor ,zip ) values (TO_DATE(:l,'yyyy-MM-dd'),:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44,:45,:46,:47,:48,:49,:50,:51,:52,:53,:54,:55,:56,:57,:58,:59,:60,:61,:62,:63,:64,:65,:66,:67,:68,:69,:70,:71,:72,:73,:74,:75,:76,:77,:78,:79,:80,:81,:82,:83,:84,:85,:86,:87,:88,:89,:90,:91,:92,:93,:94,:95,:96,:97,:98,:99,:100,:101,:102,:103,:104,:105,:106,:107,:108,:109,:110,:111,:112,:113,:114,:115,:116,:117,:118,:119,:120,:121,:122,:123,:124,:125,:126,:127,:128,:129,:130,:131,:132,:133,:134,:135,:136,:137,:138,:139,:140,:141,:142,:143,:144,:145,:146,:147,:148,:149,:150,:151,:152,:153,:154,:155,:156,:157,:158,:159,:160,:161,:162,:163,:164,:165,:166,:167,:168,:169,:170,:171,:172,:173,:174,:175,:176,:177,:178,:179,:180,:181,:182,:183,:184,:185,:186,:187,:188,:189,:190,:191,:192,:193,:194,:195,:196,:197,:198,:199,:200,:201,:202,:203,:204,:205,:206,:207,:208,:209,:210,:211,:212,:213,:214,:215,:216,:217,:218,:219,:220,:221,:222,:223,:224,:225,:226,:227,:228,:229,:230,:231,:232,:233,:234,:235,:236,:237,:238,:239,:240,:241,:242,:243,:244,:245,:246,:247,:248,:249,:250,:251,:252,:253,:254,:255,:256,:257,:258,:259,:260,:261,:262,:263,:264,:265,:266,:267,:268,:269,:270,:271,:272,:273,:274,:275,:276,:277,:278,:279,:280,:281,:282,:283,:284,:285,:286,:287,:288,:289,:290,:291,:292,:293,:294,:295,:296,:297,:298,:299,:300,:301,:302,:303,:304,:305,:306,:307,:308,:309,:310,:311,:312,:313,:314,:315,:316,:317,:318,:319,:320,:321,:322,:323,:324,:325,:326,:327,:328,:329,:330,:331,:332,:333,:334,:335,:336,:337,:338,:339,:340,:341,:342,:343,:344,:345,:346,:347,:348,:349,:350,:351,:352,:353,:354,:355,:356,:357,:358,:359,:360,:361,:362,:363,:364,:365,:366,:367,:368,:369,:370,:371,:372,:373,:374,:375,:376,:377,:378,:379,:380,:381,:382,:383,:384,:385,:386,:387,:388,:389,:390,:391,:392,:393,:394,:395,:396,:397,:398,:399,:400,:401,:402,:403,:404,:405,:406,:407,:408,:409,:410,:411,:412,:413,:414,:415,:416,:417,:418,:419,:420,:421,:422,:423,:424,:425,:426,:427,:428,:429,:430,:431,:432,:433,:434,:435,:436,:437,:438,:439,:440,:441,:442,:443,:444,:445,:446,:447,:448,:449,:450,:451,:452,:453,:454,:455,:456,:457,:458,:459,:460,:461,:462,:463,:464,:465,:466,:467,:468,:469,:470,:471,:472,:473,:474,:475,:476,:477,:478,:479,:480,:481,:482,:483,:484,:485,:486,:487,:488,:489,:490,:491,:492,:493,:494,:495,:496,:497,:498,:499,:500,:501,:502,:503,:504,:505,:506,:507,:508,:509,:510,:511,:512,:513,:514,:515,:516,:517,:518,:519,:520,:521,:522,:523,:524,:525,:526,:527,:528,:529,:530,:531,:532,:533,:534,:535,:536,:537,:538,:539,:540,:541,:542,:543,:544,:545,:546,:547,:548,:549,:550,:551,:552,:553,:554,:555,:556,:557,:558,:559,:560,:561,:562,:563,:564,:565,:566,:567,:568,:569,:570,:571,:572,:573,:574,:575,:576,:577,:578,:579,:580,:581,:582,:583,:584,:585,:586,:587,:588,:589,:590,:591,:592,:593,:594,:595,:596,:597,:598,:599,:600,:601,:602,:603,:604,:605,:606,:607,:608,:609,:610,:611,:612,:613,:614,:615,:616,:617,:618,:619,:620,:621,:622,:623,:624,:625,:626,:627,:628,:629,:630,:631,:632,:633,:634,:635,:636,:637,:638,:639,:640,:641,:642,:643,:644,:645,:646,:647,:648,:649,:650,:651,:652,:653,:654,:655,:656,:657,:658,:659,:660,:661,:662,:663,:664,:665,:666,:667,:668,:669,:670,:671,:672,:673,:674,:675,:676,:677,:678,:679,:680,:681,:682,:683,:684,:685,:686,:687,:688,:689,:690,:691,:692,:693,:694,:695,:696,:697,:698,:699,:700,:701,:702,:703,:704,:705,:706,:707,:708,:709,:710,:711,:712,:713,:714,:715,:716,:717,:718,:719,:720,:721,:722,:723,:724,:725,:726,:727,:728,:729,:730,:731,:732,:733,:734,:735,:736,:737,:738,:739,:740,:741,:742,:743,:744,:745,:746,:747,:748,:749,:750,:751,:752,:753,:754,:755,:756,:757,:758,:759,:760,:761,:762,:763,:764,:765,:766,:767,:768,:769,:770,:771,:772,:773,:774,:775,:776,:777,:778,:779,:780,:781,:782,:783,:784,:785,:786,:787,:788,:789,:790,:791,:792,:793,:794,:795,:796,:797,:798,:799,:800,:801,:802,:803,:804,:805,:806,:807,:808,:809,:810,:811,:812,:813,:814,:815,:816,:817,:818,:819,:820,:821,:822,:823,:824,:825,:826,:827,:828,:829,:830,:831,:832,:833,:834,:835,:836,:837,:838,:839,:840,:841,:842,:843,:844,:845,:846,:847,:848,:849,:850,:851,:852,:853,:854,:855,:856,:857,:858,:859,:860,:861,:862,:863,:864,:865,:866,:867,:868,:869,:870,:871,:872,:873,:874,:875,:876,:877,:878,:879,:880,:881,:882,:883,:884,:885,:886,:887,:888,:889,:890,:891,:892,:893,:894,:895,:896,:897,:898,:899,:900,:901,:902,:903,:904,:905,:906,:907,:908,:909,:910,:911,:912,:913,:914,:915,:916,:917,:918,:919,:920,:921,:922,:923,:924,:925,:926,:927,:928,:929,:930,:931,:932,:933,:934,:935,:936,:937,:938,:939,:940,:941,:942,:943,:944,:945,:946,:947,:948,:949,:950,:951,:952,:953,:954,:955,:956,:957,:958,:959,:960,:961,:962,:963,:964,:965,:966,:967,:968,:969,:970)".format(profile.replace('-','_')),
            data)
        conn.commit()
        print('insert data is ok')
    except Exception as err:
        print(err)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    # for day in days:
    #     for profile in profiles:
    #         for id in ids:
    #             zipFile = getFTPFile(id, profile, getLastDay(day))
    #             print(day, profile, id, zipFile)
    #             if(zipFile):
    #                 # deleteData(profile, getLastDay(day))
    #                 getZipData(id, profile, getLastDay(day))
    # a = [1, 2, 3]
    # b = ['a', 'b', 'c']
    # z = zip(a, b)
    json_data = {}
    # print (z)
    # for key, value in z:
    #     jsonData[key] = value
    # print (jsonData)
    uuid_ = uuid.uuid1()

    columns_name = ['id', 'hit_date', 'browser', 'campaign', 'carrier', 'country', 'cust_visid', 'date_time', 'evar1', 'evar2', 'evar3', 'evar4', 'evar5', 'evar6', 'evar7', 'evar8', 'evar9', 'evar10', 'evar11', 'evar12', 'evar13', 'evar14', 'evar15', 'evar16', 'evar17', 'evar18', 'evar19', 'evar20', 'evar21', 'evar22', 'evar23', 'evar24', 'evar25', 'evar26', 'evar27', 'evar28', 'evar29', 'evar30', 'evar31', 'evar32', 'evar33', 'evar34', 'evar35', 'evar36', 'evar37', 'evar38', 'evar39', 'evar40', 'evar41', 'evar42', 'evar43', 'evar44', 'evar45', 'evar46', 'evar47', 'evar48', 'evar49', 'evar50', 'evar51', 'evar52', 'evar53', 'evar54', 'evar55', 'evar56', 'evar57', 'evar58', 'evar59', 'evar60', 'evar61', 'evar62', 'evar63', 'evar64', 'evar65', 'evar66', 'evar67', 'evar68', 'evar69', 'evar70', 'evar71', 'evar72', 'evar73', 'evar74', 'evar75', 'evar76', 'evar77', 'evar78', 'evar79', 'evar80', 'evar81', 'evar82', 'evar83', 'evar84', 'evar85', 'event_list', 'geo_city', 'geo_country', 'ip', 'last_purchase_num', 'mcvisid', 'mobile_id', 'new_visit', 'os', 'page_event', 'page_url', 'pagename', 'product_list', 'prop1', 'prop2', 'prop3', 'prop4', 'prop5', 'prop6', 'prop7', 'prop8', 'prop9', 'prop10', 'prop11', 'prop12', 'prop13', 'prop14', 'prop15', 'prop16', 'prop17', 'prop18', 'prop19', 'prop20', 'purchaseid', 'ref_domain', 'ref_type', 'referrer', 'search_engine', 'visid_new', 'visid_type', 'visit_num', 'post_visid_high', 'post_visid_low', 'visit_start_time_gmt']
    value_tuple = (str(uuid_), '2019-06-05', "b'912718859", '', '', '113', '', '2019-06-05 08:28:57', '', 'https://jp.ceair.com/ja/booking.html?fromValue=NRT%2CHND&toValue=SHA&fromLabel=%E6%9D%B1%E4%BA%AC%2C%20NRT%2CHND&toLabel=%E8%99%B9%E6%A1%A5%2F%E4%B8%8A%E6%B5%B7%2C%20SHA&travelType=roundtrip&date1=1561561200000&date2=1561734000000&adult=1&child=0&infant=0', '', '', '', '', '', '\\xe6\\x9d\\xb1\\xe4\\xba\\xac, NRT,HND', '\\xe8\\x99\\xb9\\xe6\\xa1\\xa5/\\xe4\\xb8\\x8a\\xe6\\xb5\\xb7, SHA', '2019-06-27', '', 'roundtrip', '1', '0', '0', 'Y', '0', 'MU538', '', '', '', '', '\\xe6\\x9d\\xb1\\xe4\\xba\\xac, NRT,HND:\\xe8\\x99\\xb9\\xe6\\xa1\\xa5/\\xe4\\xb8\\x8a\\xe6\\xb5\\xb7, SHA', 'global', '2019-06-29', 'roundtrip:Y', '', '22', '', '1', 'Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36', '', '', '', '', '', '', '', '', 'S', '', '', '', '', '', 'international flight', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'cea-jp-prd,cea-global-prd', '14~30', 'jp.ceair.com', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '202,101,107,108,109,111,112,113,114,115,116,117,122,123,124,125,127,129,130,139,145,167,168,169', 'ueda', 'jpn', '202.216.114.142', '0', '00000000000000000000000000000000000000', '0', '0', '1240087047', '0', 'https://jp.ceair.com/ja/booking.html?fromValue=NRT%2CHND&toValue=SHA&fromLabel=%E6%9D%B1%E4%BA%AC%2C%20NRT%2CHND&toLabel=%E8%99%B9%E6%A1%A5%2F%E4%B8%8A%E6%B5%B7%2C%20SHA&travelType=roundtrip&date1=1561561200000&date2=1561734000000&adult=1&child=0&infant=0&byPoints=false&isRequery=false&flightClass=Y&depCityCode=TYO&arrCityCode=SHA', 'jp.ceair.com/ja/booking.html:Select_Return_Flights', 'flight_booking;NRT|HND:SHA', 'https://jp.ceair.com/ja/booking.html?fromValue=NRT%2CHND&toValue=SHA&fromLabel=%E6%9D%B1%E4%BA%AC%2C', '', '', 'ja', 'https://jp.ceair.com/ja/', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '6', '', '0', '3', 'N', '27', '3241614744842631812', '6917548314191757866', "1559694091\\n'")
    value = list(value_tuple)
    val = zip(columns_name, value)
    for key, value in val:
        json_data[key] = value
    print (json_data)






